{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "b8728e87-a02a-4f5b-b34f-d4c7286a21fc",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>地域</th>\n",
       "      <th>省份</th>\n",
       "      <th>产品年</th>\n",
       "      <th>数量</th>\n",
       "      <th>金额</th>\n",
       "      <th>市值</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A</td>\n",
       "      <td>甲</td>\n",
       "      <td>2020</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A</td>\n",
       "      <td>甲</td>\n",
       "      <td>2021</td>\n",
       "      <td>2</td>\n",
       "      <td>20</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A</td>\n",
       "      <td>乙</td>\n",
       "      <td>2022</td>\n",
       "      <td>3</td>\n",
       "      <td>30</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>B</td>\n",
       "      <td>丙</td>\n",
       "      <td>2020</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>B</td>\n",
       "      <td>丁</td>\n",
       "      <td>2021</td>\n",
       "      <td>2</td>\n",
       "      <td>20</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>B</td>\n",
       "      <td>丁</td>\n",
       "      <td>2022</td>\n",
       "      <td>3</td>\n",
       "      <td>30</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>C</td>\n",
       "      <td>戊</td>\n",
       "      <td>2020</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>C</td>\n",
       "      <td>己</td>\n",
       "      <td>2021</td>\n",
       "      <td>2</td>\n",
       "      <td>20</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>C</td>\n",
       "      <td>己</td>\n",
       "      <td>2022</td>\n",
       "      <td>3</td>\n",
       "      <td>30</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  地域 省份   产品年  数量  金额  市值\n",
       "0  A  甲  2020   1  10  15\n",
       "1  A  甲  2021   2  20  30\n",
       "2  A  乙  2022   3  30  45\n",
       "3  B  丙  2020   1  10  15\n",
       "4  B  丁  2021   2  20  30\n",
       "5  B  丁  2022   3  30  45\n",
       "6  C  戊  2020   1  10  15\n",
       "7  C  己  2021   2  20  30\n",
       "8  C  己  2022   3  30  45"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "from alive_progress import alive_bar\n",
    "import xlwings as xw\n",
    "import warnings\n",
    "\n",
    "target_file = './结果表.xlsx'\n",
    "\n",
    "df = pd.read_excel('./test.xlsx',sheet_name='Sheet1')\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "60a93811-38f6-4d4e-9ec7-ba1059507ac8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>地域</th>\n",
       "      <th>省份</th>\n",
       "      <th>产品年</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A</td>\n",
       "      <td>甲</td>\n",
       "      <td>2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A</td>\n",
       "      <td>甲</td>\n",
       "      <td>2021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A</td>\n",
       "      <td>甲</td>\n",
       "      <td>2022</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>A</td>\n",
       "      <td>乙</td>\n",
       "      <td>2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>A</td>\n",
       "      <td>乙</td>\n",
       "      <td>2021</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   地域 省份   产品年\n",
       "0   A  甲  2020\n",
       "1   A  甲  2021\n",
       "2   A  甲  2022\n",
       "18  A  乙  2020\n",
       "19  A  乙  2021"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 生成笛卡尔积\n",
    "\n",
    "df_a = pd.DataFrame(df['地域'])\n",
    "df_b = pd.DataFrame(df['省份'])\n",
    "df_c = pd.DataFrame(df['产品年'])\n",
    "\n",
    "df_a['临时列'] = 1\n",
    "df_b['临时列'] = 1\n",
    "df_c['临时列'] = 1\n",
    "  \n",
    "df_dikaer = pd.merge(df_a,df_b,on='临时列')\n",
    "df_dikaer = pd.merge(df_dikaer,df_c,on='临时列')                    \n",
    "\n",
    "df_dikaer = df_dikaer.drop('临时列',axis=1)\n",
    "df_dikaer.drop_duplicates(inplace=True)\n",
    "df_dikaer.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "f1daefc4-424e-48c0-80ba-990ab1e3fe0e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>地域</th>\n",
       "      <th>省份</th>\n",
       "      <th>产品年</th>\n",
       "      <th>数量</th>\n",
       "      <th>金额</th>\n",
       "      <th>市值</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A</td>\n",
       "      <td>甲</td>\n",
       "      <td>2020</td>\n",
       "      <td>1.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>15.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A</td>\n",
       "      <td>甲</td>\n",
       "      <td>2021</td>\n",
       "      <td>2.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>30.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A</td>\n",
       "      <td>甲</td>\n",
       "      <td>2022</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A</td>\n",
       "      <td>乙</td>\n",
       "      <td>2020</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A</td>\n",
       "      <td>乙</td>\n",
       "      <td>2021</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  地域 省份   产品年   数量    金额    市值\n",
       "0  A  甲  2020  1.0  10.0  15.0\n",
       "1  A  甲  2021  2.0  20.0  30.0\n",
       "2  A  甲  2022  0.0   0.0   0.0\n",
       "3  A  乙  2020  0.0   0.0   0.0\n",
       "4  A  乙  2021  0.0   0.0   0.0"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 关联（左连接）\n",
    "\n",
    "df = pd.merge(df_dikaer,df,on=['地域','省份','产品年'],how='left').fillna(0)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "57c61279-2ce9-4a06-aeec-c37303c8cf24",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th colspan=\"7\" halign=\"left\">市值</th>\n",
       "      <th colspan=\"7\" halign=\"left\">数量</th>\n",
       "      <th colspan=\"7\" halign=\"left\">金额</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>省份</th>\n",
       "      <th>丁</th>\n",
       "      <th>丙</th>\n",
       "      <th>乙</th>\n",
       "      <th>己</th>\n",
       "      <th>戊</th>\n",
       "      <th>甲</th>\n",
       "      <th>All</th>\n",
       "      <th>丁</th>\n",
       "      <th>丙</th>\n",
       "      <th>乙</th>\n",
       "      <th>...</th>\n",
       "      <th>戊</th>\n",
       "      <th>甲</th>\n",
       "      <th>All</th>\n",
       "      <th>丁</th>\n",
       "      <th>丙</th>\n",
       "      <th>乙</th>\n",
       "      <th>己</th>\n",
       "      <th>戊</th>\n",
       "      <th>甲</th>\n",
       "      <th>All</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>地域</th>\n",
       "      <th>产品年</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">A</th>\n",
       "      <th>2020</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>15</td>\n",
       "      <td>15.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>10</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>30</td>\n",
       "      <td>30.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>20</td>\n",
       "      <td>20.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>45</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>30</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>30.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">B</th>\n",
       "      <th>2020</th>\n",
       "      <td>0</td>\n",
       "      <td>15</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>10</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021</th>\n",
       "      <td>30</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>20</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>20.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022</th>\n",
       "      <td>45</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>30</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>30.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">C</th>\n",
       "      <th>2020</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>15</td>\n",
       "      <td>0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>10</td>\n",
       "      <td>0</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>30</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>20</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>20.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>45</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>30</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>30.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>All</th>\n",
       "      <th></th>\n",
       "      <td>75</td>\n",
       "      <td>15</td>\n",
       "      <td>45</td>\n",
       "      <td>75</td>\n",
       "      <td>15</td>\n",
       "      <td>45</td>\n",
       "      <td>270.0</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>18.0</td>\n",
       "      <td>50</td>\n",
       "      <td>10</td>\n",
       "      <td>30</td>\n",
       "      <td>50</td>\n",
       "      <td>10</td>\n",
       "      <td>30</td>\n",
       "      <td>180.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          市值                            数量        ...              金额          \\\n",
       "省份         丁   丙   乙   己   戊   甲    All  丁  丙  乙  ...  戊  甲   All   丁   丙   乙   \n",
       "地域  产品年                                           ...                           \n",
       "A   2020   0   0   0   0   0  15   15.0  0  0  0  ...  0  1   1.0   0   0   0   \n",
       "    2021   0   0   0   0   0  30   30.0  0  0  0  ...  0  2   2.0   0   0   0   \n",
       "    2022   0   0  45   0   0   0   45.0  0  0  3  ...  0  0   3.0   0   0  30   \n",
       "B   2020   0  15   0   0   0   0   15.0  0  1  0  ...  0  0   1.0   0  10   0   \n",
       "    2021  30   0   0   0   0   0   30.0  2  0  0  ...  0  0   2.0  20   0   0   \n",
       "    2022  45   0   0   0   0   0   45.0  3  0  0  ...  0  0   3.0  30   0   0   \n",
       "C   2020   0   0   0   0  15   0   15.0  0  0  0  ...  1  0   1.0   0   0   0   \n",
       "    2021   0   0   0  30   0   0   30.0  0  0  0  ...  0  0   2.0   0   0   0   \n",
       "    2022   0   0   0  45   0   0   45.0  0  0  0  ...  0  0   3.0   0   0   0   \n",
       "All       75  15  45  75  15  45  270.0  5  1  3  ...  1  3  18.0  50  10  30   \n",
       "\n",
       "                             \n",
       "省份         己   戊   甲    All  \n",
       "地域  产品年                      \n",
       "A   2020   0   0  10   10.0  \n",
       "    2021   0   0  20   20.0  \n",
       "    2022   0   0   0   30.0  \n",
       "B   2020   0   0   0   10.0  \n",
       "    2021   0   0   0   20.0  \n",
       "    2022   0   0   0   30.0  \n",
       "C   2020   0  10   0   10.0  \n",
       "    2021  20   0   0   20.0  \n",
       "    2022  30   0   0   30.0  \n",
       "All       50  10  30  180.0  \n",
       "\n",
       "[10 rows x 21 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_pivot = pd.pivot_table(df,index=['地域','产品年'],columns=['省份'],values=['数量','金额','市值'],aggfunc='sum', margins=True,fill_value=0)\n",
    "df_pivot"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "6e84cf7b-ee3b-4440-8987-a09998a657eb",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>地域</th>\n",
       "      <th>产品年</th>\n",
       "      <th>省份</th>\n",
       "      <th>市值</th>\n",
       "      <th>数量</th>\n",
       "      <th>金额</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A</td>\n",
       "      <td>2020</td>\n",
       "      <td>小计</td>\n",
       "      <td>15.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A</td>\n",
       "      <td>2020</td>\n",
       "      <td>丁</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A</td>\n",
       "      <td>2020</td>\n",
       "      <td>丙</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A</td>\n",
       "      <td>2020</td>\n",
       "      <td>乙</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A</td>\n",
       "      <td>2020</td>\n",
       "      <td>己</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58</th>\n",
       "      <td>C</td>\n",
       "      <td>2022</td>\n",
       "      <td>丙</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59</th>\n",
       "      <td>C</td>\n",
       "      <td>2022</td>\n",
       "      <td>乙</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60</th>\n",
       "      <td>C</td>\n",
       "      <td>2022</td>\n",
       "      <td>己</td>\n",
       "      <td>45.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>30.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61</th>\n",
       "      <td>C</td>\n",
       "      <td>2022</td>\n",
       "      <td>戊</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>62</th>\n",
       "      <td>C</td>\n",
       "      <td>2022</td>\n",
       "      <td>甲</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>63 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   地域   产品年  省份    市值   数量    金额\n",
       "0   A  2020  小计  15.0  1.0  10.0\n",
       "1   A  2020   丁   0.0  0.0   0.0\n",
       "2   A  2020   丙   0.0  0.0   0.0\n",
       "3   A  2020   乙   0.0  0.0   0.0\n",
       "4   A  2020   己   0.0  0.0   0.0\n",
       ".. ..   ...  ..   ...  ...   ...\n",
       "58  C  2022   丙   0.0  0.0   0.0\n",
       "59  C  2022   乙   0.0  0.0   0.0\n",
       "60  C  2022   己  45.0  3.0  30.0\n",
       "61  C  2022   戊   0.0  0.0   0.0\n",
       "62  C  2022   甲   0.0  0.0   0.0\n",
       "\n",
       "[63 rows x 6 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_subtotal = df_pivot.stack('省份').fillna(0).reset_index()\n",
    "df_subtotal = df_subtotal[df_subtotal['地域']!='All']\n",
    "df_subtotal['省份'] = df_subtotal['省份'].str.replace('All','小计')\n",
    "df_subtotal"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "989060d6-8fc2-4538-b483-e8b285ec6dcf",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th colspan=\"4\" halign=\"left\">市值</th>\n",
       "      <th colspan=\"4\" halign=\"left\">数量</th>\n",
       "      <th colspan=\"4\" halign=\"left\">金额</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>产品年</th>\n",
       "      <th>2020</th>\n",
       "      <th>2021</th>\n",
       "      <th>2022</th>\n",
       "      <th>总计</th>\n",
       "      <th>2020</th>\n",
       "      <th>2021</th>\n",
       "      <th>2022</th>\n",
       "      <th>总计</th>\n",
       "      <th>2020</th>\n",
       "      <th>2021</th>\n",
       "      <th>2022</th>\n",
       "      <th>总计</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>地域</th>\n",
       "      <th>省份</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"7\" valign=\"top\">A</th>\n",
       "      <th>丁</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>丙</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>乙</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>30.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>小计</th>\n",
       "      <td>15.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>90.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>60.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>己</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>戊</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>甲</th>\n",
       "      <td>15.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>30.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"7\" valign=\"top\">B</th>\n",
       "      <th>丁</th>\n",
       "      <td>0.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>75.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>50.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>丙</th>\n",
       "      <td>15.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>乙</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>小计</th>\n",
       "      <td>15.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>90.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>60.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>己</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>戊</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>甲</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"7\" valign=\"top\">C</th>\n",
       "      <th>丁</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>丙</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>乙</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>小计</th>\n",
       "      <td>15.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>90.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>60.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>己</th>\n",
       "      <td>0.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>75.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>50.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>戊</th>\n",
       "      <td>15.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>甲</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>总计</th>\n",
       "      <th></th>\n",
       "      <td>45.0</td>\n",
       "      <td>90.0</td>\n",
       "      <td>135.0</td>\n",
       "      <td>270.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>90.0</td>\n",
       "      <td>180.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         市值                       数量                    金额                   \n",
       "产品年    2020  2021   2022     总计 2020 2021 2022    总计  2020  2021  2022     总计\n",
       "地域 省份                                                                        \n",
       "A  丁    0.0   0.0    0.0    0.0  0.0  0.0  0.0   0.0   0.0   0.0   0.0    0.0\n",
       "   丙    0.0   0.0    0.0    0.0  0.0  0.0  0.0   0.0   0.0   0.0   0.0    0.0\n",
       "   乙    0.0   0.0   45.0   45.0  0.0  0.0  3.0   3.0   0.0   0.0  30.0   30.0\n",
       "   小计  15.0  30.0   45.0   90.0  1.0  2.0  3.0   6.0  10.0  20.0  30.0   60.0\n",
       "   己    0.0   0.0    0.0    0.0  0.0  0.0  0.0   0.0   0.0   0.0   0.0    0.0\n",
       "   戊    0.0   0.0    0.0    0.0  0.0  0.0  0.0   0.0   0.0   0.0   0.0    0.0\n",
       "   甲   15.0  30.0    0.0   45.0  1.0  2.0  0.0   3.0  10.0  20.0   0.0   30.0\n",
       "B  丁    0.0  30.0   45.0   75.0  0.0  2.0  3.0   5.0   0.0  20.0  30.0   50.0\n",
       "   丙   15.0   0.0    0.0   15.0  1.0  0.0  0.0   1.0  10.0   0.0   0.0   10.0\n",
       "   乙    0.0   0.0    0.0    0.0  0.0  0.0  0.0   0.0   0.0   0.0   0.0    0.0\n",
       "   小计  15.0  30.0   45.0   90.0  1.0  2.0  3.0   6.0  10.0  20.0  30.0   60.0\n",
       "   己    0.0   0.0    0.0    0.0  0.0  0.0  0.0   0.0   0.0   0.0   0.0    0.0\n",
       "   戊    0.0   0.0    0.0    0.0  0.0  0.0  0.0   0.0   0.0   0.0   0.0    0.0\n",
       "   甲    0.0   0.0    0.0    0.0  0.0  0.0  0.0   0.0   0.0   0.0   0.0    0.0\n",
       "C  丁    0.0   0.0    0.0    0.0  0.0  0.0  0.0   0.0   0.0   0.0   0.0    0.0\n",
       "   丙    0.0   0.0    0.0    0.0  0.0  0.0  0.0   0.0   0.0   0.0   0.0    0.0\n",
       "   乙    0.0   0.0    0.0    0.0  0.0  0.0  0.0   0.0   0.0   0.0   0.0    0.0\n",
       "   小计  15.0  30.0   45.0   90.0  1.0  2.0  3.0   6.0  10.0  20.0  30.0   60.0\n",
       "   己    0.0  30.0   45.0   75.0  0.0  2.0  3.0   5.0   0.0  20.0  30.0   50.0\n",
       "   戊   15.0   0.0    0.0   15.0  1.0  0.0  0.0   1.0  10.0   0.0   0.0   10.0\n",
       "   甲    0.0   0.0    0.0    0.0  0.0  0.0  0.0   0.0   0.0   0.0   0.0    0.0\n",
       "总计     45.0  90.0  135.0  270.0  3.0  6.0  9.0  18.0  30.0  60.0  90.0  180.0"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 注意！此时“列”的纵向总计由于小计的存在，数值是实际值的 2 倍，需要后续处理（横向是无问题的）\n",
    "\n",
    "df_pivot = pd.pivot_table(df_subtotal,index=['地域','省份'],columns=['产品年'],values=['数量','金额','市值'],aggfunc='sum',fill_value=0, margins=True,margins_name='总计')\n",
    "df_pivot = df_pivot.drop(('总计','' ), axis=0)\n",
    "df_pivot.loc[('总计','' ),:] = df_pivot.sum(axis=0) / 2\n",
    "df_pivot"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "6e75c87c-8643-4fa0-bb93-7f807da2ca9e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th>产品年</th>\n",
       "      <th>地域</th>\n",
       "      <th>省份</th>\n",
       "      <th colspan=\"3\" halign=\"left\">2020</th>\n",
       "      <th colspan=\"3\" halign=\"left\">2021</th>\n",
       "      <th colspan=\"3\" halign=\"left\">2022</th>\n",
       "      <th colspan=\"3\" halign=\"left\">总计</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>数量</th>\n",
       "      <th>金额</th>\n",
       "      <th>市值</th>\n",
       "      <th>数量</th>\n",
       "      <th>金额</th>\n",
       "      <th>市值</th>\n",
       "      <th>数量</th>\n",
       "      <th>金额</th>\n",
       "      <th>市值</th>\n",
       "      <th>数量</th>\n",
       "      <th>金额</th>\n",
       "      <th>市值</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A</td>\n",
       "      <td>丁</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A</td>\n",
       "      <td>丙</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A</td>\n",
       "      <td>乙</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>45.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A</td>\n",
       "      <td>小计</td>\n",
       "      <td>1.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>90.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A</td>\n",
       "      <td>己</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>A</td>\n",
       "      <td>戊</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>A</td>\n",
       "      <td>甲</td>\n",
       "      <td>1.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>45.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>B</td>\n",
       "      <td>丁</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>75.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>B</td>\n",
       "      <td>丙</td>\n",
       "      <td>1.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>15.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>B</td>\n",
       "      <td>乙</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>B</td>\n",
       "      <td>小计</td>\n",
       "      <td>1.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>90.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>B</td>\n",
       "      <td>己</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>B</td>\n",
       "      <td>戊</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>B</td>\n",
       "      <td>甲</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>C</td>\n",
       "      <td>丁</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>C</td>\n",
       "      <td>丙</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>C</td>\n",
       "      <td>乙</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>C</td>\n",
       "      <td>小计</td>\n",
       "      <td>1.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>90.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>C</td>\n",
       "      <td>己</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>75.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>C</td>\n",
       "      <td>戊</td>\n",
       "      <td>1.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>15.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>C</td>\n",
       "      <td>甲</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>总计</td>\n",
       "      <td></td>\n",
       "      <td>3.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>90.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>90.0</td>\n",
       "      <td>135.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>180.0</td>\n",
       "      <td>270.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "产品年  地域  省份 2020             2021             2022                 总计         \\\n",
       "              数量    金额    市值   数量    金额    市值   数量    金额     市值    数量     金额   \n",
       "0     A   丁  0.0   0.0   0.0  0.0   0.0   0.0  0.0   0.0    0.0   0.0    0.0   \n",
       "1     A   丙  0.0   0.0   0.0  0.0   0.0   0.0  0.0   0.0    0.0   0.0    0.0   \n",
       "2     A   乙  0.0   0.0   0.0  0.0   0.0   0.0  3.0  30.0   45.0   3.0   30.0   \n",
       "3     A  小计  1.0  10.0  15.0  2.0  20.0  30.0  3.0  30.0   45.0   6.0   60.0   \n",
       "4     A   己  0.0   0.0   0.0  0.0   0.0   0.0  0.0   0.0    0.0   0.0    0.0   \n",
       "5     A   戊  0.0   0.0   0.0  0.0   0.0   0.0  0.0   0.0    0.0   0.0    0.0   \n",
       "6     A   甲  1.0  10.0  15.0  2.0  20.0  30.0  0.0   0.0    0.0   3.0   30.0   \n",
       "7     B   丁  0.0   0.0   0.0  2.0  20.0  30.0  3.0  30.0   45.0   5.0   50.0   \n",
       "8     B   丙  1.0  10.0  15.0  0.0   0.0   0.0  0.0   0.0    0.0   1.0   10.0   \n",
       "9     B   乙  0.0   0.0   0.0  0.0   0.0   0.0  0.0   0.0    0.0   0.0    0.0   \n",
       "10    B  小计  1.0  10.0  15.0  2.0  20.0  30.0  3.0  30.0   45.0   6.0   60.0   \n",
       "11    B   己  0.0   0.0   0.0  0.0   0.0   0.0  0.0   0.0    0.0   0.0    0.0   \n",
       "12    B   戊  0.0   0.0   0.0  0.0   0.0   0.0  0.0   0.0    0.0   0.0    0.0   \n",
       "13    B   甲  0.0   0.0   0.0  0.0   0.0   0.0  0.0   0.0    0.0   0.0    0.0   \n",
       "14    C   丁  0.0   0.0   0.0  0.0   0.0   0.0  0.0   0.0    0.0   0.0    0.0   \n",
       "15    C   丙  0.0   0.0   0.0  0.0   0.0   0.0  0.0   0.0    0.0   0.0    0.0   \n",
       "16    C   乙  0.0   0.0   0.0  0.0   0.0   0.0  0.0   0.0    0.0   0.0    0.0   \n",
       "17    C  小计  1.0  10.0  15.0  2.0  20.0  30.0  3.0  30.0   45.0   6.0   60.0   \n",
       "18    C   己  0.0   0.0   0.0  2.0  20.0  30.0  3.0  30.0   45.0   5.0   50.0   \n",
       "19    C   戊  1.0  10.0  15.0  0.0   0.0   0.0  0.0   0.0    0.0   1.0   10.0   \n",
       "20    C   甲  0.0   0.0   0.0  0.0   0.0   0.0  0.0   0.0    0.0   0.0    0.0   \n",
       "21   总计      3.0  30.0  45.0  6.0  60.0  90.0  9.0  90.0  135.0  18.0  180.0   \n",
       "\n",
       "产品年         \n",
       "        市值  \n",
       "0      0.0  \n",
       "1      0.0  \n",
       "2     45.0  \n",
       "3     90.0  \n",
       "4      0.0  \n",
       "5      0.0  \n",
       "6     45.0  \n",
       "7     75.0  \n",
       "8     15.0  \n",
       "9      0.0  \n",
       "10    90.0  \n",
       "11     0.0  \n",
       "12     0.0  \n",
       "13     0.0  \n",
       "14     0.0  \n",
       "15     0.0  \n",
       "16     0.0  \n",
       "17    90.0  \n",
       "18    75.0  \n",
       "19    15.0  \n",
       "20     0.0  \n",
       "21   270.0  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 自定义列字段的顺序（适用于多层索引的调换顺序、排序）\n",
    "\n",
    "# 交换层级\n",
    "df_pivot = df_pivot.reorder_levels([1,0],axis=1)\n",
    "\n",
    "# # 自定义顺序\n",
    "# 构建元组\n",
    "\n",
    "cols_new = []\n",
    "\n",
    "a = [2020,2021,2022,'总计']\n",
    "b = ['数量','金额','市值']\n",
    "\n",
    "for i in a:\n",
    "    for m in b:\n",
    "        list_temp = []\n",
    "        list_temp.append(i)\n",
    "        list_temp.append(m)\n",
    "        cols_new.append(tuple(list_temp))\n",
    "        \n",
    "df_pivot = df_pivot[cols_new]\n",
    "\n",
    "# df_pivot = df_pivot.sort_index(axis=1)\n",
    "df_pivot = df_pivot.astype('float').reset_index()\n",
    "df_pivot"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "5f3cd333-6893-439f-b41d-99ac608eb445",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th>产品年</th>\n",
       "      <th>地域</th>\n",
       "      <th>省份</th>\n",
       "      <th colspan=\"3\" halign=\"left\">2020</th>\n",
       "      <th colspan=\"3\" halign=\"left\">2021</th>\n",
       "      <th colspan=\"3\" halign=\"left\">2022</th>\n",
       "      <th colspan=\"3\" halign=\"left\">总计</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>数量</th>\n",
       "      <th>金额</th>\n",
       "      <th>市值</th>\n",
       "      <th>数量</th>\n",
       "      <th>金额</th>\n",
       "      <th>市值</th>\n",
       "      <th>数量</th>\n",
       "      <th>金额</th>\n",
       "      <th>市值</th>\n",
       "      <th>数量</th>\n",
       "      <th>金额</th>\n",
       "      <th>市值</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>A</td>\n",
       "      <td>甲</td>\n",
       "      <td>1.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>45.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A</td>\n",
       "      <td>乙</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>45.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A</td>\n",
       "      <td>丙</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A</td>\n",
       "      <td>丁</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>A</td>\n",
       "      <td>戊</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A</td>\n",
       "      <td>己</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A</td>\n",
       "      <td>小计</td>\n",
       "      <td>1.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>90.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>B</td>\n",
       "      <td>甲</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>B</td>\n",
       "      <td>乙</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>B</td>\n",
       "      <td>丙</td>\n",
       "      <td>1.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>15.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>B</td>\n",
       "      <td>丁</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>75.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>B</td>\n",
       "      <td>戊</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>B</td>\n",
       "      <td>己</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>B</td>\n",
       "      <td>小计</td>\n",
       "      <td>1.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>90.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>C</td>\n",
       "      <td>甲</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>C</td>\n",
       "      <td>乙</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>C</td>\n",
       "      <td>丙</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>C</td>\n",
       "      <td>丁</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>C</td>\n",
       "      <td>戊</td>\n",
       "      <td>1.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>15.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>C</td>\n",
       "      <td>己</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>75.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>C</td>\n",
       "      <td>小计</td>\n",
       "      <td>1.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>90.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>总计</td>\n",
       "      <td></td>\n",
       "      <td>3.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>90.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>90.0</td>\n",
       "      <td>135.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>180.0</td>\n",
       "      <td>270.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "产品年  地域  省份 2020             2021             2022                 总计         \\\n",
       "              数量    金额    市值   数量    金额    市值   数量    金额     市值    数量     金额   \n",
       "6     A   甲  1.0  10.0  15.0  2.0  20.0  30.0  0.0   0.0    0.0   3.0   30.0   \n",
       "2     A   乙  0.0   0.0   0.0  0.0   0.0   0.0  3.0  30.0   45.0   3.0   30.0   \n",
       "1     A   丙  0.0   0.0   0.0  0.0   0.0   0.0  0.0   0.0    0.0   0.0    0.0   \n",
       "0     A   丁  0.0   0.0   0.0  0.0   0.0   0.0  0.0   0.0    0.0   0.0    0.0   \n",
       "5     A   戊  0.0   0.0   0.0  0.0   0.0   0.0  0.0   0.0    0.0   0.0    0.0   \n",
       "4     A   己  0.0   0.0   0.0  0.0   0.0   0.0  0.0   0.0    0.0   0.0    0.0   \n",
       "3     A  小计  1.0  10.0  15.0  2.0  20.0  30.0  3.0  30.0   45.0   6.0   60.0   \n",
       "13    B   甲  0.0   0.0   0.0  0.0   0.0   0.0  0.0   0.0    0.0   0.0    0.0   \n",
       "9     B   乙  0.0   0.0   0.0  0.0   0.0   0.0  0.0   0.0    0.0   0.0    0.0   \n",
       "8     B   丙  1.0  10.0  15.0  0.0   0.0   0.0  0.0   0.0    0.0   1.0   10.0   \n",
       "7     B   丁  0.0   0.0   0.0  2.0  20.0  30.0  3.0  30.0   45.0   5.0   50.0   \n",
       "12    B   戊  0.0   0.0   0.0  0.0   0.0   0.0  0.0   0.0    0.0   0.0    0.0   \n",
       "11    B   己  0.0   0.0   0.0  0.0   0.0   0.0  0.0   0.0    0.0   0.0    0.0   \n",
       "10    B  小计  1.0  10.0  15.0  2.0  20.0  30.0  3.0  30.0   45.0   6.0   60.0   \n",
       "20    C   甲  0.0   0.0   0.0  0.0   0.0   0.0  0.0   0.0    0.0   0.0    0.0   \n",
       "16    C   乙  0.0   0.0   0.0  0.0   0.0   0.0  0.0   0.0    0.0   0.0    0.0   \n",
       "15    C   丙  0.0   0.0   0.0  0.0   0.0   0.0  0.0   0.0    0.0   0.0    0.0   \n",
       "14    C   丁  0.0   0.0   0.0  0.0   0.0   0.0  0.0   0.0    0.0   0.0    0.0   \n",
       "19    C   戊  1.0  10.0  15.0  0.0   0.0   0.0  0.0   0.0    0.0   1.0   10.0   \n",
       "18    C   己  0.0   0.0   0.0  2.0  20.0  30.0  3.0  30.0   45.0   5.0   50.0   \n",
       "17    C  小计  1.0  10.0  15.0  2.0  20.0  30.0  3.0  30.0   45.0   6.0   60.0   \n",
       "21   总计      3.0  30.0  45.0  6.0  60.0  90.0  9.0  90.0  135.0  18.0  180.0   \n",
       "\n",
       "产品年         \n",
       "        市值  \n",
       "6     45.0  \n",
       "2     45.0  \n",
       "1      0.0  \n",
       "0      0.0  \n",
       "5      0.0  \n",
       "4      0.0  \n",
       "3     90.0  \n",
       "13     0.0  \n",
       "9      0.0  \n",
       "8     15.0  \n",
       "7     75.0  \n",
       "12     0.0  \n",
       "11     0.0  \n",
       "10    90.0  \n",
       "20     0.0  \n",
       "16     0.0  \n",
       "15     0.0  \n",
       "14     0.0  \n",
       "19    15.0  \n",
       "18    75.0  \n",
       "17    90.0  \n",
       "21   270.0  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 自定义行字段的顺序（手动指定），自动指定用 sort_index()\n",
    "\n",
    "type1 = pd.CategoricalDtype(categories=['A','B','C','总计'],ordered=True)\n",
    "type2 = pd.CategoricalDtype(categories=['甲','乙','丙','丁','戊','己','小计',''],ordered=True)\n",
    "df_pivot[('地域',   '')] = df_pivot[('地域',   '')].astype(type1)  #将指定列转成自定义的type\n",
    "df_pivot[('省份',   '')] = df_pivot[('省份',   '')].astype(type2)\n",
    "df_pivot.sort_values(by=[('地域',   ''),('省份',   '')],inplace=True)\n",
    "df_pivot"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "09c1c6b2-84da-43f8-97e1-64963f037dc2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>地域</th>\n",
       "      <th>省份</th>\n",
       "      <th>2020 数量</th>\n",
       "      <th>2020 金额</th>\n",
       "      <th>2020 市值</th>\n",
       "      <th>2021 数量</th>\n",
       "      <th>2021 金额</th>\n",
       "      <th>2021 市值</th>\n",
       "      <th>2022 数量</th>\n",
       "      <th>2022 金额</th>\n",
       "      <th>2022 市值</th>\n",
       "      <th>总计 数量</th>\n",
       "      <th>总计 金额</th>\n",
       "      <th>总计 市值</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A</td>\n",
       "      <td>甲</td>\n",
       "      <td>1.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>45.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A</td>\n",
       "      <td>乙</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>45.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A</td>\n",
       "      <td>丙</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A</td>\n",
       "      <td>丁</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A</td>\n",
       "      <td>戊</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>A</td>\n",
       "      <td>己</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>A</td>\n",
       "      <td>小计</td>\n",
       "      <td>1.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>90.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>B</td>\n",
       "      <td>甲</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>B</td>\n",
       "      <td>乙</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>B</td>\n",
       "      <td>丙</td>\n",
       "      <td>1.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>15.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>B</td>\n",
       "      <td>丁</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>75.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>B</td>\n",
       "      <td>戊</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>B</td>\n",
       "      <td>己</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>B</td>\n",
       "      <td>小计</td>\n",
       "      <td>1.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>90.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>C</td>\n",
       "      <td>甲</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>C</td>\n",
       "      <td>乙</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>C</td>\n",
       "      <td>丙</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>C</td>\n",
       "      <td>丁</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>C</td>\n",
       "      <td>戊</td>\n",
       "      <td>1.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>15.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>C</td>\n",
       "      <td>己</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>75.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>C</td>\n",
       "      <td>小计</td>\n",
       "      <td>1.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>90.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>总计</td>\n",
       "      <td></td>\n",
       "      <td>3.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>90.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>90.0</td>\n",
       "      <td>135.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>180.0</td>\n",
       "      <td>270.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   地域  省份   2020 数量  2020 金额  2020 市值  2021 数量  2021 金额  2021 市值  2022 数量  \\\n",
       "0    A   甲      1.0     10.0     15.0      2.0     20.0     30.0      0.0   \n",
       "1    A   乙      0.0      0.0      0.0      0.0      0.0      0.0      3.0   \n",
       "2    A   丙      0.0      0.0      0.0      0.0      0.0      0.0      0.0   \n",
       "3    A   丁      0.0      0.0      0.0      0.0      0.0      0.0      0.0   \n",
       "4    A   戊      0.0      0.0      0.0      0.0      0.0      0.0      0.0   \n",
       "5    A   己      0.0      0.0      0.0      0.0      0.0      0.0      0.0   \n",
       "6    A  小计      1.0     10.0     15.0      2.0     20.0     30.0      3.0   \n",
       "7    B   甲      0.0      0.0      0.0      0.0      0.0      0.0      0.0   \n",
       "8    B   乙      0.0      0.0      0.0      0.0      0.0      0.0      0.0   \n",
       "9    B   丙      1.0     10.0     15.0      0.0      0.0      0.0      0.0   \n",
       "10   B   丁      0.0      0.0      0.0      2.0     20.0     30.0      3.0   \n",
       "11   B   戊      0.0      0.0      0.0      0.0      0.0      0.0      0.0   \n",
       "12   B   己      0.0      0.0      0.0      0.0      0.0      0.0      0.0   \n",
       "13   B  小计      1.0     10.0     15.0      2.0     20.0     30.0      3.0   \n",
       "14   C   甲      0.0      0.0      0.0      0.0      0.0      0.0      0.0   \n",
       "15   C   乙      0.0      0.0      0.0      0.0      0.0      0.0      0.0   \n",
       "16   C   丙      0.0      0.0      0.0      0.0      0.0      0.0      0.0   \n",
       "17   C   丁      0.0      0.0      0.0      0.0      0.0      0.0      0.0   \n",
       "18   C   戊      1.0     10.0     15.0      0.0      0.0      0.0      0.0   \n",
       "19   C   己      0.0      0.0      0.0      2.0     20.0     30.0      3.0   \n",
       "20   C  小计      1.0     10.0     15.0      2.0     20.0     30.0      3.0   \n",
       "21  总计          3.0     30.0     45.0      6.0     60.0     90.0      9.0   \n",
       "\n",
       "    2022 金额  2022 市值  总计 数量  总计 金额  总计 市值  \n",
       "0       0.0      0.0    3.0   30.0   45.0  \n",
       "1      30.0     45.0    3.0   30.0   45.0  \n",
       "2       0.0      0.0    0.0    0.0    0.0  \n",
       "3       0.0      0.0    0.0    0.0    0.0  \n",
       "4       0.0      0.0    0.0    0.0    0.0  \n",
       "5       0.0      0.0    0.0    0.0    0.0  \n",
       "6      30.0     45.0    6.0   60.0   90.0  \n",
       "7       0.0      0.0    0.0    0.0    0.0  \n",
       "8       0.0      0.0    0.0    0.0    0.0  \n",
       "9       0.0      0.0    1.0   10.0   15.0  \n",
       "10     30.0     45.0    5.0   50.0   75.0  \n",
       "11      0.0      0.0    0.0    0.0    0.0  \n",
       "12      0.0      0.0    0.0    0.0    0.0  \n",
       "13     30.0     45.0    6.0   60.0   90.0  \n",
       "14      0.0      0.0    0.0    0.0    0.0  \n",
       "15      0.0      0.0    0.0    0.0    0.0  \n",
       "16      0.0      0.0    0.0    0.0    0.0  \n",
       "17      0.0      0.0    0.0    0.0    0.0  \n",
       "18      0.0      0.0    1.0   10.0   15.0  \n",
       "19     30.0     45.0    5.0   50.0   75.0  \n",
       "20     30.0     45.0    6.0   60.0   90.0  \n",
       "21     90.0    135.0   18.0  180.0  270.0  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 姜两层列名恢复成单层\n",
    "\n",
    "df_pivot.columns =[str(s1) +' '+ str(s2) for (s1,s2) in df_pivot.columns.tolist()]\n",
    "df_pivot = df_pivot.reset_index(drop=True)\n",
    "df_pivot"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "404a42ff-193c-42c1-a4cf-2137c798d3bb",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "|████████████████████████████████████████| 1/1 [100%] in 9.4s (0.11/s) \n",
      "\n",
      "表格输出完成！！\n"
     ]
    }
   ],
   "source": [
    "# 持久化输出\n",
    "\n",
    "with pd.ExcelWriter(target_file) as writer:\n",
    "    df_pivot.to_excel(writer,sheet_name='result', index=False)\n",
    "\n",
    "app = xw.App(visible=False,add_book=False)\n",
    "workbook = app.books.open('./结果表.xlsx')\n",
    "\n",
    "with alive_bar(len(workbook.sheets)) as bar:    \n",
    "    for i in workbook.sheets:\n",
    "        bar()\n",
    "        # pbar.set_description(f'Processing {i}')\n",
    "    # 批量设置格式（行高、列宽、字体、大小、线框）\n",
    "        value = i.range('A1').expand('table')# 选择要调整的区域\n",
    "        value.rows.autofit() # 调整列宽字符宽度\n",
    "        value.columns.autofit()  # 调整行高字符宽度\n",
    "        value.api.Font.Name = '微软雅黑' # 设置字体\n",
    "        value.api.Font.Size = 9 # 设置字号大小（磅数）\n",
    "        value.api.VerticalAlignment = xw.constants.VAlign.xlVAlignCenter # 设置垂直居中\n",
    "        value.api.HorizontalAlignment = xw.constants.HAlign.xlHAlignCenter # 设置水平居中\n",
    "        for cell in value:\n",
    "            for b in range(7,12):\n",
    "                cell.api.Borders(b).LineStyle = 1 # 设置单元格边框线型\n",
    "                cell.api.Borders(b).Weight = 2 # 设置单元格边框粗细\n",
    "        value = i.range('A1').expand('right')  # 选择要调整的区域\n",
    "        value.api.Font.Size = 10\n",
    "        value.api.Font.Bold = True  # 设置为粗体\n",
    "        # print(f'《{i.name}》 页面处理完成……')\n",
    "workbook.save()\n",
    "workbook.close()\n",
    "app.quit()\n",
    "\n",
    "print(f'\\n表格输出完成！！')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
